package com.hnkywl.sts.dao.tjbb;

import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import oracle.jdbc.OracleCallableStatement;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.io.UnsupportedEncodingException;
import java.sql.*;
import java.util.*;
import java.util.Date;

/**
 * Created with IntelliJ IDEA.
 * User: SDE09
 * Date: 13-9-26
 * Time: 下午12:46
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class YwsfcDao extends SimpleHibernateTemplate {
    /**
     * 业务收发存报表
     *
     * @return
     */
    public Map<String, Object> ywsfcList(Integer gsid,  Date begindate, Date enddt,Integer wz_type,String wzdl, Integer ck_id,
            Integer pageNo, Integer pageSize,Integer select_type, Integer dc_flag) {
        //1、gs_id 若为空  则等于 -1
          if (gsid == null)
            gsid = -1;
        Integer v_gsid;
        v_gsid = gsid;
        //ck_id
        if (ck_id == null)
            ck_id = -1;
        Integer v_ck_id;
        v_ck_id = ck_id;
        //=========
        //得到月初
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(enddt);
        calendar.add(Calendar.DAY_OF_MONTH, calendar.getActualMinimum(Calendar.DAY_OF_MONTH));
        java.sql.Date paramDate = new java.sql.Date(begindate.getTime());
        java.sql.Date paramDate1 = new java.sql.Date(calendar.getTime().getTime());
        //=========
       /* Calendar calendar = Calendar.getInstance();
        calendar.setTime(enddt);
        calendar.add(Calendar.DATE, 1);
        java.sql.Date paramDate = new java.sql.Date(begindate.getTime());
        java.sql.Date paramDate1 = new java.sql.Date(calendar.getTime().getTime());*/
        //2、将开始时间转换成数字  20130801
       /* begindate = 20130801;
        enddt = 20130930;*/
        //3、将截止时间转换成数字  20130830

        if (wzdl == null || "".equals(wzdl))
            wzdl = "-1";
        //8、select_type   数量 = 1  金额 = 2
        // 9、普通查询 -1   若为导出 则等于 1
        dc_flag = -1 ;

        Connection con = null;
        CallableStatement call = null;
        ResultSet rs = null;
        Integer total = 0;
        try {
            con = getSession().connection();
            call = con.prepareCall("{Call P_RPT_YW_SFC_ZX(?,?,?,?,?,?,?,?,?,?,?,?)}");
            call.setInt(1, gsid);
            call.setDate(2, paramDate);
            call.setDate(3, paramDate1);
            call.setInt(4, wz_type);
            call.setInt(5, ck_id);
            call.setString(6, wzdl);
            call.setInt(7, (pageNo - 1) * pageSize);
            call.setInt(8, pageNo * pageSize);
            call.setInt(9, select_type);
            call.setInt(10, dc_flag);
            call.registerOutParameter(11, Types.NUMERIC);
            call.registerOutParameter(12, oracle.jdbc.OracleTypes.CURSOR);
            call.execute();
            //====================================
            total = call.getInt(11);
            rs = ((OracleCallableStatement) call).getCursor(12);
            ResultSetMetaData resultsMetaData = rs.getMetaData();
            int columnCount = resultsMetaData.getColumnCount();
          //  Map<String, List> result = new HashMap<String, List>();
            Map<String, Object> result = new HashMap<String, Object>();
            List<Map> rows = new ArrayList<Map>();
            List<Map> footer = new ArrayList<Map>();
            while (rs.next()) {
                Map<String, Object> r = new HashMap<String, Object>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = resultsMetaData.getColumnName(i).toLowerCase();
                    r.put(columnName, rs.getObject(i));
                }
                if (!"合计".equals(r.get("gs_name").toString()))
                    rows.add(r);
                else
                    footer.add(r);
            }
            result.put("total", total);
            result.put("rows", rows);
            result.put("footer", footer);
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            closeResource(rs, call, con);
        }
    }
    private void closeResource(ResultSet rs, Statement s, Connection con) {
        if (rs != null)
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (s != null)
            try {
                s.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (con != null)
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
    }

    /**
     * 导出业务收发存报表
     * @param gsid
     * @param begindate
     * @param enddt
     * @param wz_type
     * @param wzdl
     * @param ck_id
     * @param pageNo
     * @param pageSize
     * @param select_type
     * @param dc_flag
     * @return
     */
    public Map<String, Object> exportywsfcList(Integer gsid,  Date begindate, Date enddt,Integer wz_type,String wzdl, Integer ck_id,
                                         Integer pageNo, Integer pageSize,Integer select_type, Integer dc_flag) {
        //1、gs_id 若为空  则等于 -1
        if (gsid == null)
            gsid = -1;
        Integer v_gsid;
        v_gsid = gsid;
        //ck_id
        if (ck_id == null)
            ck_id = -1;
        Integer v_ck_id;
        v_ck_id = ck_id;
        //=========
        //得到月初
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(enddt);
        calendar.add(Calendar.DAY_OF_MONTH, calendar.getActualMinimum(Calendar.DAY_OF_MONTH));
        java.sql.Date paramDate = new java.sql.Date(begindate.getTime());
        java.sql.Date paramDate1 = new java.sql.Date(calendar.getTime().getTime());
        //=========
        /* Calendar calendar = Calendar.getInstance();
    calendar.setTime(enddt);
    calendar.add(Calendar.DATE, 1);
    java.sql.Date paramDate = new java.sql.Date(begindate.getTime());
    java.sql.Date paramDate1 = new java.sql.Date(calendar.getTime().getTime());*/
        //2、将开始时间转换成数字  20130801
        /* begindate = 20130801;
      enddt = 20130930;*/
        //3、将截止时间转换成数字  20130830

        if (wzdl == null || "".equals(wzdl))
            wzdl = "-1";
        //8、select_type   数量 = 1  金额 = 2
        // 9、普通查询 -1   若为导出 则等于 1
        dc_flag = 1 ;

        Connection con = null;
        CallableStatement call = null;
        ResultSet rs = null;
        Integer total = 0;
        try {
            con = getSession().connection();
            call = con.prepareCall("{Call P_RPT_YW_SFC_ZX(?,?,?,?,?,?,?,?,?,?,?,?)}");
            call.setInt(1, gsid);
            call.setDate(2, paramDate);
            call.setDate(3, paramDate1);
            call.setInt(4, wz_type);
            call.setInt(5, ck_id);
            call.setString(6, wzdl);
            call.setInt(7, (pageNo - 1) * pageSize);
            call.setInt(8, pageNo * pageSize);
            call.setInt(9, select_type);
            call.setInt(10, dc_flag);
            call.registerOutParameter(11, Types.NUMERIC);
            call.registerOutParameter(12, oracle.jdbc.OracleTypes.CURSOR);
            call.execute();
            //====================================
            total = call.getInt(11);
            rs = ((OracleCallableStatement) call).getCursor(12);
            ResultSetMetaData resultsMetaData = rs.getMetaData();
            int columnCount = resultsMetaData.getColumnCount();
            //  Map<String, List> result = new HashMap<String, List>();
            Map<String, Object> result = new HashMap<String, Object>();
            List<Map> rows = new ArrayList<Map>();
            List<Map> footer = new ArrayList<Map>();
            while (rs.next()) {
                Map<String, Object> r = new HashMap<String, Object>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = resultsMetaData.getColumnName(i).toLowerCase();
                    r.put(columnName, rs.getObject(i));
                }
                if (!"合计".equals(r.get("gs_name").toString()))
                    rows.add(r);
                else
                    footer.add(r);
            }
            result.put("total", total);
            result.put("rows", rows);
            result.put("footer", footer);
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            closeResource(rs, call, con);
        }
    }

    /**
     * 本期入库采购
     * @param gsid
     * @param select_type
     * @param
     * @param begindate
     * @param enddt
     * @return
     */
    public List<Map> findbqrkcg(Integer gsid,Integer select_type,Integer ck_id,Integer wz_type ,String pz, Date begindate, Date enddt) {
        if (ck_id == null)
            ck_id = -1;
        String sql = "select gs_name," +
                "           to_char(rksj, 'yyyy-mm-dd') rksj," +
                "           cgdh," +
                "           decode(sftzd,1,'是','否') sftzd," +
                "           gys_name," +
                "           code," +
                "           oldcode," +
                "           wz_name," +
                "           ggxh," +
                "           th," +
                "           rksl," +
                "           bhsj," +
                "           je," +
                "           ck_name \n" +
                "   from v_rpt_yw_sfc_cgrk " +
                "  where ( gs_id = :gsid or bm_id = :gsid ) " +
                "  and type = :type " +
                "  and (-1 = :ck_id or ck_id = :ck_id) " +
                "  and  decode(:wz_type,1,pz_1,2,pz_2,3,pz_3,4,pz_4) = :pz " +
                //"  and  decode(:wz_type,1,pz_1,2,pz_2,3,pz_3) = :pz " +
                "  and rksj between :startDate and  :endDate\n" +
                "  union all\n" +
                "  select " +
                "   '合计:', " +
                "   null, " +
                "   null," +
                "   null," +
                "   null," +
                "   null," +
                "   null," +
                "   null," +
                "   null," +
                "   null," +
                "   sum(rksl)," +
                "   null," +
                "   sum(je)," +
                "   null    \n" +
                "  from v_rpt_yw_sfc_cgrk " +
                "  where ( gs_id = :gsid or bm_id = :gsid ) " +
                "  and type = :type " +
                "  and (-1 = :ck_id or ck_id = :ck_id) " +
                "  and  decode(:wz_type,1,pz_1,2,pz_2,3,pz_3,4,pz_4) = :pz " +
              //  "  and  decode(:wz_type,1,pz_1,2,pz_2,3,pz_3) = :pz " +
                "  and rksj between :startDate and  :endDate\n" ;

        SQLQuery sqlQuery = createSQLQuery(sql);
        sqlQuery.setParameter("gsid",gsid);
        sqlQuery.setParameter("type",select_type);
        sqlQuery.setParameter("ck_id",ck_id);
        sqlQuery.setParameter("wz_type",wz_type);
        sqlQuery.setParameter("pz",pz);
        sqlQuery.setParameter("startDate",begindate);
        sqlQuery.setParameter("endDate",enddt);
        List<Map> result = sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return result;
    }

    /**
     *  本期出库销售
     * @param gsid
     * @param select_type
     * @param
     * @param begindate
     * @param enddt
     * @return
     */
    public List<Map> findbqckck(Integer gsid,Integer select_type,Integer ck_id,Integer wz_type,String pz, Date begindate, Date enddt) {
        if (ck_id == null)
            ck_id = -1;
        String sql = "select gs_name," +
                "     to_char(cksj, 'yyyy-mm-dd') cksj," +
                "     xsdh," +
                "     decode(sftzd,1,'是','否') sftzd," +
                "     kh_name," +
                "     code," +
                "     oldcode," +
                "      wz_name," +
                "      ggxh,th," +
                "       cksl," +
                "      bhsj," +
                "      je,\n " +
                "      ck_name \n" +
                "   from v_rpt_yw_sfc_xsck " +
                "  where ( gs_id = :gsid or bm_id = :gsid ) " +
                "  and type = :type " +
                "  and (-1 = :ck_id or ck_id = :ck_id) " +
                "  and  decode(:wz_type,1,pz_1,2,pz_2,3,pz_3,4,pz_4) = :pz " +
               // "  and  decode(:wz_type,1,pz_1,2,pz_2,3,pz_3) = :pz " +
                "  and cksj between :startDate and  :endDate\n" +
                "  union all\n" +
                "   select '合计:'," +
                "   null," +
                "   null," +
                "   null," +
                "    null," +
                "    null," +
                "    null," +
                "    null," +
                "    null," +
                "    null," +
                "    sum(cksl)," +
                "    null," +
                "    sum(je), \n" +
                "    null " +
                "   from v_rpt_yw_sfc_xsck " +
                "  where ( gs_id = :gsid or bm_id = :gsid ) " +
                "  and type = :type " +
                "  and (-1 = :ck_id or ck_id = :ck_id) " +
                "  and  decode(:wz_type,1,pz_1,2,pz_2,3,pz_3,4,pz_4) = :pz " +
              //  "  and  decode(:wz_type,1,pz_1,2,pz_2,3,pz_3) = :pz " +
                "  and cksj between :startDate and  :endDate\n";
        SQLQuery sqlQuery = createSQLQuery(sql);
        sqlQuery.setParameter("gsid",gsid);
        sqlQuery.setParameter("type",select_type);
        sqlQuery.setParameter("ck_id",ck_id);
        sqlQuery.setParameter("wz_type",wz_type);
        sqlQuery.setParameter("pz",pz);
        sqlQuery.setParameter("startDate",begindate);
        sqlQuery.setParameter("endDate",enddt);
        List<Map> result = sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return result;
    }

    /**
     * 本期入库调整
     * @param gsid
     * @param select_type
     * @param
     * @param begindate
     * @param enddt
     * @return
     */
    public List<Map> findbqrkrktz(Integer gsid,Integer select_type,Integer ck_id,Integer wz_type,String pz, Date begindate, Date enddt) {
        if (ck_id == null)
            ck_id = -1;
        String sql = "select gs_name,\n" +
                "       dj_type,\n" +
                "       djh,\n" +
                "       to_char(sj, 'yyyy-mm-dd') sj,\n" +
                "       decode(sftzd, 1, '是', '否') sftzd,\n" +
                "       code,\n" +
                "       wz_name,\n" +
                "       ggxh,\n" +
                "       th,\n" +
                "       rksl,\n" +
                "       hsj,\n" +
                "       je," +
                "      ck_name \n" +
                "   from v_rpt_yw_sfc_rktz " +
                "     where ( gs_id = :gsid or bm_id = :gsid ) " +
                "      and type = :type " +
                "  and (-1 = :ck_id or ck_id = :ck_id) " +
                "  and  decode(:wz_type,1,pz_1,2,pz_2,3,pz_3,4,pz_4) = :pz " +
            //    "  and  decode(:wz_type,1,pz_1,2,pz_2,3,pz_3) = :pz " +
                "     and sj between :startDate and  :endDate\n" +
                "   union all\n" +
                "   select '合计:', null, null,null,null,null,null,null,null,sum(rksl),null,sum(je),null \n" +
                "   from v_rpt_yw_sfc_rktz " +
                "    where ( gs_id = :gsid or bm_id = :gsid ) " +
                "    and type = :type " +
                "  and (-1 = :ck_id or ck_id = :ck_id) " +
                "  and  decode(:wz_type,1,pz_1,2,pz_2,3,pz_3,4,pz_4) = :pz " +
              //  "  and  decode(:wz_type,1,pz_1,2,pz_2,3,pz_3) = :pz " +
                "   and sj  between :startDate and :endDate\n";
        //String sql="";
        SQLQuery sqlQuery = createSQLQuery(sql);
        sqlQuery.setParameter("gsid",gsid);
        sqlQuery.setParameter("type",select_type);
        sqlQuery.setParameter("ck_id",ck_id);
        sqlQuery.setParameter("wz_type",wz_type);
        sqlQuery.setParameter("pz",pz);
        sqlQuery.setParameter("startDate",begindate);
        sqlQuery.setParameter("endDate",enddt);
        List<Map> result = sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return result;
    }

    /**
     * 本期出库调整
     * @param gsid
     * @param select_type
     * @param
     * @param begindate
     * @param enddt
     * @return
     */
    public List<Map> findbqckcktz(Integer gsid,Integer select_type,Integer ck_id,Integer wz_type,String pz, Date begindate, Date enddt) {
        if (ck_id == null)
            ck_id = -1;
        String sql = "select gs_name,\n" +
                "       dj_type,\n" +
                "       djh,\n" +
                "       to_char(sj, 'yyyy-mm-dd') sj,\n" +
                "       decode(sftzd, 1, '是', '否') sftzd,\n" +
                "       code,\n" +
                "       wz_name,\n" +
                "       ggxh,\n" +
                "       th,\n" +
                "       cksl,\n" +
                "       hsj,\n" +
                "       je," +
                "      ck_name \n" +
                "    from v_rpt_yw_sfc_cktz " +
                "    where ( gs_id = :gsid or bm_id = :gsid ) " +
                "    and type = :type " +
                "  and (-1 = :ck_id or ck_id = :ck_id) " +
                "  and  decode(:wz_type,1,pz_1,2,pz_2,3,pz_3,4,pz_4) = :pz " +
              //  "  and  decode(:wz_type,1,pz_1,2,pz_2,3,pz_3) = :pz " +
                "    and sj  between :startDate and  :endDate\n" +
                "    union all\n" +
                "   select '合计:', null, null,null,null,null,null,null,null,sum(cksl),null,sum(je),null \n" +
                "   from v_rpt_yw_sfc_cktz " +
                "   where ( gs_id = :gsid or bm_id = :gsid ) " +
                "   and type = :type" +
                "  and (-1 = :ck_id or ck_id = :ck_id) " +
                "  and  decode(:wz_type,1,pz_1,2,pz_2,3,pz_3,4,pz_4) = :pz " +
               // "  and  decode(:wz_type,1,pz_1,2,pz_2,3,pz_3) = :pz " +
                "   and sj  between :startDate and :endDate\n";
        //String sql="";
        SQLQuery sqlQuery = createSQLQuery(sql);
        sqlQuery.setParameter("gsid",gsid);
        sqlQuery.setParameter("type",select_type);
        sqlQuery.setParameter("ck_id",ck_id);
        sqlQuery.setParameter("wz_type",wz_type);
        sqlQuery.setParameter("pz",pz);
        sqlQuery.setParameter("startDate",begindate);
        sqlQuery.setParameter("endDate",enddt);
        List<Map> result = sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return result;
    }

    /**
     * 本期入库拨入
     * @param gsid
     * @param select_type
     * @param
     * @param begindate
     * @param enddt
     * @return
     */
     public List<Map> findbqrkzr(Integer gsid,Integer select_type,Integer ck_id,Integer wz_type,String pz, Date begindate, Date enddt) {
         if (ck_id == null)
             ck_id = -1;
         String sql = "select khbm,\n" +
                 "        to_char(zkrq, 'yyyy-mm-dd') zkrq,\n" +
                 "        code,\n" +
                 "        wz_name,\n" +
                 "       ggxh,\n" +
                 "         th,\n" +
                 "         zrsl,\n" +
                 "       zrck,\n" +
                 "       zcck, " +
                 "      ck_name \n" +
                 "   from v_rpt_yw_sfc_zr \n" +
                 "   where ( gs_id = :gsid or bm_id = :gsid ) \n " +
                 "   and type = :type \n" +
                 "  and (-1 = :ck_id or ck_id = :ck_id) " +
                 "  and  decode(:wz_type,1,pz_1,2,pz_2,3,pz_3,4,pz_4) = :pz " +
               //  "  and  decode(:wz_type,1,pz_1,2,pz_2,3,pz_3) = :pz " +
                 "   and zkrq between :startDate and  :endDate \n" +
                 "   and kh_rkrq  <= :endDate \n" +
                 "   union all\n" +
                 "   select '合计:', null, null,null,null,null,sum(zrsl),null,null,null \n" +
                 "   from v_rpt_yw_sfc_zr " +
                 "   where ( gs_id = :gsid or bm_id = :gsid ) \n " +
                 "   and type = :type \n" +
                 "  and (-1 = :ck_id or ck_id = :ck_id) " +
                 "  and  decode(:wz_type,1,pz_1,2,pz_2,3,pz_3,4,pz_4) = :pz " +
                // "  and  decode(:wz_type,1,pz_1,2,pz_2,3,pz_3) = :pz " +
                 "   and zkrq between :startDate and  :endDate \n" +
                 "   and kh_rkrq  <= :endDate \n";
         SQLQuery sqlQuery = createSQLQuery(sql);
         sqlQuery.setParameter("gsid",gsid);
         sqlQuery.setParameter("type",select_type);
         sqlQuery.setParameter("ck_id",ck_id);
         sqlQuery.setParameter("wz_type",wz_type);
         sqlQuery.setParameter("pz",pz);
         sqlQuery.setParameter("startDate",begindate);
         sqlQuery.setParameter("endDate",enddt);
         List<Map> result = sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
         return result;
     }


    /**
     * 本期出库拨出
     * @param gsid
     * @param select_type
     * @param
     * @param begindate
     * @param enddt
     * @return
     */
    public List<Map> findbqckzc(Integer gsid,Integer select_type,Integer ck_id,Integer wz_type,String pz, Date begindate, Date enddt) {
        if (ck_id == null)
            ck_id = -1;
        String sql = "select khbm,\n" +
                "        to_char(zkrq, 'yyyy-mm-dd') zkrq,\n" +
                "        code,\n" +
                "        wz_name,\n" +
                "       ggxh,\n" +
                "         th,\n" +
                "         zcsl,\n" +
                "       zrck,\n" +
                "       zcck, " +
                "      ck_name \n" +
                "   from v_rpt_yw_sfc_zc \n" +
                "   where ( gs_id = :gsid or bm_id = :gsid ) \n " +
                "   and type = :type \n" +
                "  and (-1 = :ck_id or ck_id = :ck_id) " +
                "  and  decode(:wz_type,1,pz_1,2,pz_2,3,pz_3,4,pz_4) = :pz " +
               // "  and  decode(:wz_type,1,pz_1,2,pz_2,3,pz_3) = :pz " +
                "   and zkrq between :startDate and  :endDate \n" +
                "   and kh_rkrq  <= :endDate \n" +
                "   union all\n" +
                "   select '合计:', null, null,null,null,null,sum(zcsl),null,null,null\n" +
                "   from v_rpt_yw_sfc_zc " +
                "   where ( gs_id = :gsid or bm_id = :gsid ) \n " +
                "   and type = :type \n" +
                "  and (-1 = :ck_id or ck_id = :ck_id) " +
                "  and  decode(:wz_type,1,pz_1,2,pz_2,3,pz_3,4,pz_4) = :pz " +
              //  "  and  decode(:wz_type,1,pz_1,2,pz_2,3,pz_3) = :pz " +
                "   and zkrq between :startDate and  :endDate \n" +
                "   and kh_rkrq  <= :endDate \n";
        SQLQuery sqlQuery = createSQLQuery(sql);
        sqlQuery.setParameter("gsid",gsid);
        sqlQuery.setParameter("type",select_type);
        sqlQuery.setParameter("ck_id",ck_id);
        sqlQuery.setParameter("wz_type",wz_type);
        sqlQuery.setParameter("pz",pz);
        sqlQuery.setParameter("startDate",begindate);
        sqlQuery.setParameter("endDate",enddt);
        List<Map> result = sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return result;
    }

    /**
     * 本期出库内部领用
     * @param gsid
     * @param select_type
     * @param
     * @param begindate
     * @param enddt
     * @return
     */
    public List<Map> findbqcknbly(Integer gsid,Integer select_type,Integer ck_id,Integer wz_type,String pz, Date begindate, Date enddt) {
        if (ck_id == null)
            ck_id = -1;
        String sql = "select  to_char(flrq, 'yyyy-mm-dd') flrq,\n" +
                "        code,\n" +
                "        wz_name,\n" +
                "       ggxh,\n" +
                "         th,\n" +
                "         flr,\n" +
                "       llgs,\n" +
                "       llbm, " +
                "         flr,\n" +
                "       ck,\n" +
                "       lysl, " +
                "      ck_name \n" +
                "   from v_rpt_yw_sfc_nbll \n" +
                "   where ( gs_id = :gsid or bm_id = :gsid ) \n " +
                "   and type = :type \n" +
                "  and (-1 = :ck_id or ck_id = :ck_id) " +
                "  and  decode(:wz_type,1,pz_1,2,pz_2,3,pz_3,4,pz_4) = :pz " +
              //  "  and  decode(:wz_type,1,pz_1,2,pz_2,3,pz_3) = :pz " +
                "   and flrq between :startDate and  :endDate \n" +
                "   and rkrq  <= :endDate \n" +
                "   union all\n" +
                "   select '合计:', null, null,null,null,null,null,null,null,null,sum(lysl),null \n" +
                "   from v_rpt_yw_sfc_nbll \n" +
                "   where ( gs_id = :gsid or bm_id = :gsid ) \n " +
                "   and type = :type \n" +
                "  and (-1 = :ck_id or ck_id = :ck_id) " +
                "  and  decode(:wz_type,1,pz_1,2,pz_2,3,pz_3,4,pz_4) = :pz " +
               // "  and  decode(:wz_type,1,pz_1,2,pz_2,3,pz_3) = :pz " +
                "   and flrq between :startDate and  :endDate \n" +
                "   and rkrq  <= :endDate \n" ;
        SQLQuery sqlQuery = createSQLQuery(sql);
        sqlQuery.setParameter("gsid",gsid);
        sqlQuery.setParameter("type",select_type);
        sqlQuery.setParameter("ck_id",ck_id);
        sqlQuery.setParameter("wz_type",wz_type);
        sqlQuery.setParameter("pz",pz);
        sqlQuery.setParameter("startDate",begindate);
        sqlQuery.setParameter("endDate",enddt);
        List<Map> result = sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return result;
    }
}
